package com.grm.common.utils;

import com.grm.common.annotation.ExcelColumn;
import com.grm.common.annotation.ExcelColumnType;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

@Slf4j
public class ExcelUtils {

    /**
     * 导出Excel文件
     *
     * @param data     数据列表
     * @param response HttpServletResponse对象
     * @param <T>      数据类型
     * @throws IOException IO异常
     */
    public static <T> void exportExcel(List<T> data, HttpServletResponse response) throws IOException {

        // 获取类的注解信息
        Class<?> clazz = data.get(0).getClass();
        Workbook workbook = writeDataListToWorkBook(data, clazz);
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition",
                "attachment; filename=" + DateUtils.getCurrentLocalDateTimeStr() + ".xlsx");

        // 输出Excel文件
        try (OutputStream outputStream = response.getOutputStream()) {
            workbook.write(outputStream);
        }
    }

    /**
     * 导出Excel文件到本地指定路径
     *
     * @param data     数据列表
     * @param filePath 导出文件的路径
     * @param <T>      数据类型
     * @throws IOException IO异常
     */
    public static <T> void exportExcelToLocal(List<T> data, String filePath) throws IOException {
        // 获取类的注解信息
        Class<?> clazz = data.get(0).getClass();
        Workbook workbook = writeDataListToWorkBook(data, clazz);

        // 判断filePath是文件夹还是文件
        File file = new File(filePath);
        if (file.isDirectory()) {
            String fileName = DateUtils.getCurrentLocalDateTimeStr() + ".xlsx";
            String newFilePath = Paths.get(filePath, fileName).toString();
            log.info("excel file path = {}", newFilePath);
            File excelFile = new File(newFilePath);
            // 删除已存在的文件
            if (excelFile.exists()) {
                excelFile.delete();
            }
            // 输出Excel文件到本地
            try (OutputStream outputStream = new FileOutputStream(excelFile)) {
                workbook.write(outputStream);
            }
        } else {
            // 删除已存在的文件
            if (file.exists()) {
                file.delete();
            }
            // 输出Excel文件到本地
            try (OutputStream outputStream = new FileOutputStream(file)) {
                workbook.write(outputStream);
            }
        }
    }

    /**
     * 导入Excel文件
     *
     * @param file  上传的Excel文件
     * @param clazz 数据类型的Class对象
     * @param <T>   数据类型
     * @return 导入的数据列表
     * @throws IOException IO异常
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz) throws IOException {
        List<T> data = new ArrayList<>();

        try (InputStream inputStream = file.getInputStream()) {
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            Field[] fields = clazz.getDeclaredFields();
            List<Field> annotatedFields = new ArrayList<>();
            for (Field field : fields) {
                ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                if (excelColumn != null) {
                    annotatedFields.add(field);
                }
            }
            // 根据列顺序排序
            annotatedFields.sort(Comparator.comparingInt(f -> f.getAnnotation(ExcelColumn.class).order()));

            for (Row row : sheet) {
                if (row.getRowNum() >= 1) {
                    T obj;
                    try {
                        Constructor<T> constructor = clazz.getDeclaredConstructor();
                        obj = constructor.newInstance();
                    } catch (NoSuchMethodException | InstantiationException | IllegalAccessException |
                             InvocationTargetException e) {
                        e.printStackTrace();
                        continue;
                    }
                    for (int i = 0; i < annotatedFields.size(); i++) {
                        Field field = annotatedFields.get(i);
                        ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                        Cell cell = row.getCell(i);
                        if (cell != null) {
                            field.setAccessible(true);
                            try {
                                Object value = getCellValue(cell, excelColumn.type());
                                field.set(obj, value);
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            }
                        }
                    }

                    data.add(obj);
                }
            }
        }

        return data;
    }

    private static <T> Workbook writeDataListToWorkBook(List<T> data, Class<?> clazz) {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet("Sheet1");

        // 设置表头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 设置背景颜色为浅灰色
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充模式为实心
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 13); // 设置字体大小
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.LEFT); // 设置水平对齐方式为左对齐

        // 设置表头
        Row headerRow = sheet.createRow(0);
        Field[] fields = clazz.getDeclaredFields();
        List<Field> annotatedFields = new ArrayList<>();
        for (Field field : fields) {
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            if (excelColumn != null) {
                annotatedFields.add(field);
            }
        }
        // 根据列顺序排序
        annotatedFields.sort(Comparator.comparingInt(f -> f.getAnnotation(ExcelColumn.class).order()));
        for (int i = 0; i < annotatedFields.size(); i++) {
            Field field = annotatedFields.get(i);
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            // 创建单元格
            Cell cell = headerRow.createCell(i);
            // 设置单元格的值为注解的value
            cell.setCellValue(excelColumn.value());
            // 设置单元格样式为表头样式
            cell.setCellStyle(headerStyle);
            // 根据自定义注解设置列宽
            int columnWidth = excelColumn.width();
            sheet.setColumnWidth(i, columnWidth);
        }

        // 填充数据
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i + 1);
            T obj = data.get(i);
            for (int j = 0; j < annotatedFields.size(); j++) {
                Field field = annotatedFields.get(j);
                ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                Cell cell = row.createCell(j);
                field.setAccessible(true);
                try {
                    Object value = field.get(obj);
                    if (value != null) {
                        // 根据列类型设置单元格的值
                        setCellValue(cell, value, excelColumn.type());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }

    /**
     * 根据列类型设置单元格的值
     *
     * @param cell  单元格对象
     * @param value 值
     * @param type  列类型
     */
    private static void setCellValue(Cell cell, Object value, ExcelColumnType type) {
        Workbook workbook = cell.getSheet().getWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();

        switch (type) {
            case STRING:
                cell.setCellValue(value.toString());
                break;
            case NUMBER:
                if (value instanceof Number number) {
                    cell.setCellValue(String.valueOf(number));
                }
                break;
            case DATE:
                if (value instanceof Date date) {
                    String dateStr = DateUtils.dateToDateStr(date, DateUtils.DATE_FORMAT);
                    cell.setCellValue(dateStr);
                } else if (value instanceof LocalDateTime localDateTime) {
                    String dateStr = DateUtils.dateToDateStr(DateUtils.localDateTimeToDate(localDateTime), DateUtils.DATE_FORMAT);
                    cell.setCellValue(dateStr);
                }
                break;
            case BOOLEAN:
                if (value instanceof Boolean bool) {
                    cell.setCellValue(String.valueOf(bool));
                }
                break;
        }

        cell.setCellStyle(cellStyle);
    }

    /**
     * 根据列类型获取单元格的值
     *
     * @param cell 单元格对象
     * @param type 列类型
     * @return 单元格的值
     */
    private static Object getCellValue(Cell cell, ExcelColumnType type) {
        switch (type) {
            case STRING:
                return cell.getStringCellValue();
            case DATE:
                String dateStr = cell.getStringCellValue();
                if (!StringUtils.isEmpty(dateStr)) {
                    return DateUtils.dateStringToDate(dateStr);
                } else {
                    return null;
                }
            case NUMBER:
                if (cell.getCellType() == CellType.NUMERIC) {
                    double numericCellValue = cell.getNumericCellValue();
                    return (int) numericCellValue;
                }else{
                    String stringCellValue = cell.getStringCellValue();
                    try {
                        return Integer.parseInt(stringCellValue);
                    } catch (NumberFormatException e) {
                        double value = Double.parseDouble(stringCellValue);
                        return (int) value;
                    }
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
        }
        return null;
    }
}
